use master
go

create database bbs
on
(
	name='bbs',
	filename='F:\bbs.mdf',
	size=10,
	maxsize=100,
	filegrowth=10%
)
log on
(
	name='bbs_log',
	filename='F:\bbs_log.ldf',
	size=10,
	maxsize=100,
	filegrowth=10%
)
go

use bbs
go
create table bbsUsers
(
	UID int identity(1,1),
	uName varchar(10) not null,
	uSex varchar(2) not null,
	uAge int not null,
	uPOINT int not null
)
alter table bbsUsers add constraint PK_bbsUsers_UID primary key(UID)
alter table bbsUsers add constraint UK_bbsUsers_uName unique(uName)
alter table bbsUsers add constraint CK_bbsUsers_uSex check(uSex='男' or uSex='女')
alter table bbsUsers add constraint CK_bbsUsers_uAge check(uAge>=15 and uAge<=60)
alter table bbsUsers add constraint CK_bbsUsers_uPoint check(uPoint>=0)


create table bbsSection
(
	sID int identity(1,1),
	sName varchar(10) not null,
	sUid int
)
alter table bbsSection add constraint PK_bbsSection_sID primary key(sID)
alter table bbsSection add constraint FK_bbsSection_sUid foreign key(sUid) references bbsUsers(UID) 


create table bbsTopic
(
	tID int primary key identity(1,1),
	tUID int references bbsUsers(UID),
	tSID int references bbsSection(sID),
	TtITLE varchar(100) not null,
	tMsg text not null,
	tTime datetime,
	tCount int
)

create table bbsReplay
(
	rID int primary key identity(1,1),
	rUID int references bbsUsers(UID),
	rTID  int references bbsTopic(tID),
	rMsg text not null,
	rTime datetime
)

insert into bbsUsers values ('小雨点','女',20,0),
('逍遥','男',18,4),('七年级生','男',19,2)

SELECT * FROM bbsTopic

select uName,uPoint into bbsPoint from bbsUsers
insert into bbsSection values ('技术交流',1),('读书世界',3),
('生活八卦',1),('八卦区',3)

insert into bbsTopic values (2,1,'范跑跑','谁是范跑跑',2008-7-8,1),
(3,2,',NET','与JAVA的区别是什么呀？',2008-9-1,2),
(1,3,'九年夏天最流行什么','有谁知道今年夏天最流行什么呀？',2008-9-10,0)

insert into bbsReplay values (3,1,'就是一地震就跑的那个',2008-7-8),
(1,4,'目前不知道',2008-9-1),
(2,5,'吹空调',2012-11-8)

alter table bbsTopic drop constraint FK__bbsTopic__tUID__1920BF5C
alter table bbsReplay drop constraint FK__bbsReplay__rUID__1CF15040
delete from bbsUsers where UID=2

update bbsUsers set uPOINT=10 WHERE UID=1
alter table bbsTopic drop constraint FK__bbsTopic__tSID__1A14E395
delete from bbsSection where sID=1

truncate table bbsReplay

select * from bbsUsers
select * from bbsTopic
select * from bbsSection
select * from bbsPoint

--1.在主贴表中统计每个版块的发帖总数
select tSID, COUNT(tID) 发帖总数 from bbsTopic GROUP BY tSID

--2.在回帖表中统计每个主贴的回帖总数量
select rTID, COUNT(rID) 回帖总数 from bbsReplay GROUP BY rTID

--3.在主贴表中统计每个用户的发的主帖的总数
select tUID, COUNT(tID) 主帖总数 from bbsTopic group by tUID

--4.在主贴表中统计每个用户发的主贴的回复数量总和
select tUID, SUM(tCount) 回复总和 from bbsTopic group by tUID

--5.在主贴表中查询每个版块的主贴的平均回复数量大于3的版块的平均回复数量
select tSID, AVG(tCount) 平均回复数量 from bbsTopic group by tSID, tCount having AVG(tCount)>3

--6.在用户信息表中查询出积分最高的用户的用户名，性别，年龄和积分
select uName, uPOINT 积分最高, uSex, uAge from bbsUsers where uPOINT=(select max(uPOINT) from bbsUsers)

--7.在主贴表中（bbsTopic）中将帖子的内容或标题中有“快乐”两字的记录查询出来
select * from bbsTopic where TtITLE like '%快乐&'

--8.在用户信息表（bbsUsers）中将用户年龄在15-20之间并且积分在10分以上的优秀用户查询出来（用多种方法实现）
select * from bbsUsers group by UID, uName, uSex, uAge, uPOINT having uPOINT>10 and uAge<20 and uAge>15
select * from bbsUsers where uPOINT>10 and uAge<20 and uAge>15
select * from bbsUsers where (uAge between 15 and 20)and uPOINT>10

--9.在用户信息表（bbsUsers）中将用户名的第一个字为“小”，第三字为“大”的用户信息查询出来
select * from bbsUsers where uName like '小_大'

--10.在主贴表（bbsTopic）中将在2008-9-10 12:00:00 以后发的并且回复数量在10以上的帖子的标题和内容查询出来，并且为列取上对应的中文列名
select TtITLE 标题, tMsg 内容 from bbsTopic where tTime>'2008-9-10 12:00:00' and tCount>10

--11.在主贴表（bbsTopic）中将帖子的标题是以‘！’结尾的帖子的发帖人编号和回复数量查询出来
select * from bbsTopic where TtITLE like '%!'